package kjh820909.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import kjh820909.interfaces.SungService;
import kjh820909.model.SungJukVO;



public class JDBCSungJuk implements SungService {

	// JDBC 접속정보
		private static final String DRV ="oracle.jdbc.OracleDriver";
		private static final String URL ="jdbc:oracle:thin:@//localhost:1521/xe";
		private static final String USR ="scott";
		private static final String PWD ="TIGER";
		
		// SQL문 작성
		private static final String insertSQL =
				"INSERT INTO sungjuk" +
				" (sjno,name,kor,eng,mat) " +
				" VALUES (SJNO.nextval,?,?,?,?) ";
		private static final String selectSQL =
				" select * from sungjuk "
				+ " order by sjno desc";
		
		private static final String deleteSQl =
				" delete from sungjuk where sjno = ? ";
		
		private static final String updateSQL =
				" update sungjuk set name = ?, kor =?, eng = ?, mat = ? where sjno = ?";

		@Override
		public boolean newSungJuk(SungJukVO sj) {
			Connection conn = null;
			PreparedStatement ps = null;
			boolean result = false;
			try{
				Class.forName(DRV);
				conn = DriverManager.getConnection(URL, USR, PWD);
				ps = conn.prepareStatement(insertSQL);
				 // ? 매개변수에 들어갈 실제 값을 지정
					ps.setString(1, sj.getName());
					ps.setInt(2, sj.getKor());
					ps.setInt(3, sj.getEng());
					ps.setInt(4, sj.getMat());
					
					
				int cnt = ps.executeUpdate();
				// 성공적으로 insert 되면 result를 true로
				if(cnt>0) result = true;
				
			} catch (Exception ex){
				ex.printStackTrace();
			}finally{
				if(ps != null)
				try{ps.close();}catch(Exception e){}
				if(conn != null)
				try{conn.close();}catch(Exception e){}
				
			}
			return result;
		}

		@Override
		public String readSungJuk() {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			String result = "";
			
			try{
				Class.forName(DRV);
				conn = DriverManager.getConnection(URL, USR, PWD);
				ps = conn.prepareStatement(selectSQL);
				rs = ps.executeQuery();
				
				// selectSQL 실행결과를 모두 출력
				while(rs.next()){
					for(int i=1; i<=9; ++i)
						result += rs.getString(i);
					result += "\n";
				}
			} catch(Exception ex){
				ex.printStackTrace();
			}finally{
				if(rs != null) try{rs.close();}catch(Exception e){}
				if(ps != null) try{ps.close();}catch(Exception e){}
				if(conn != null) try{conn.close();}catch(Exception e){}
			}
			return result;
		}

		@Override
		public boolean modifySungJuk(SungJukVO sj) {
			Connection conn = null;
			PreparedStatement ps = null;
			boolean result = false;
				// 성공적으로 수정이되었는지 확인용
			
			try{
				Class.forName(DRV);
				conn = DriverManager.getConnection(URL,USR,PWD);
				ps=conn.prepareStatement(updateSQL);
					ps.setString(1, sj.getName());
					ps.setInt(2, sj.getKor());
					ps.setInt(3, sj.getEng());
					ps.setInt(4, sj.getMat());
					ps.setString(5, sj.getSjno());
				int cnt = ps.executeUpdate();
				if (cnt>0)result =true;
			} catch(Exception ex){
				ex.printStackTrace();
			} finally{
				if(ps!=null)try{ps.close();}catch(Exception e){}
				if(conn!=null)try{conn.close();}catch(Exception e){}
			}
			
			return result;
		}

		@Override
		public boolean removeSungJuk(String key) {
			Connection conn = null;
			PreparedStatement ps = null;
			boolean result = false;
			// 성공적으로 삭제되었는지 확인용
			
			try{
				Class.forName(DRV);
				conn = DriverManager.getConnection(URL, USR, PWD);
				ps = conn.prepareStatement(deleteSQl);
					ps.setString(1, key); // 첫번째 ?자리에 들어갈 값 지정
				int cnt = ps.executeUpdate();
					// sql문 실행 후 결과 반환
				if(cnt>0)result = true;
				} catch (Exception ex){
					ex.printStackTrace();
				} finally{
					if(ps!=null)try{ps.close();}catch(Exception e){}
					if(conn!=null)try{conn.close();}catch(Exception e){}
				}
			return result;
		}
		
		
}
